import re
import os
import pymysql
import numpy as np
from iotdb.dbapi import connect
import templates

# 将csi存入mysql

com_json=templates.DATA_JSON()
db_iotdb=templates.DB_IOTDB()
db_mysql=templates.DB_MYSQL()


class OPEN_MYSQL:
    def open_mysql(self):
        db_mysql.Connect()
        self.cursor = db_mysql.con.cursor()

    def close_mysql(self):
        self.cursor.close()
        db_mysql.con.close()

    def inquire_sql(self,sql):
        try:
            self.cursor.execute(sql)
            # 获取所有查询结果
            results = self.cursor.fetchall()
            return results[0][0]
            # print("执行成功")
        except Exception as e:
            print(e)
        finally:
            # 提交
            db_mysql.con.commit()

    # 核心贡献值活跃度（贡献值人数）
    def CCA_sql(self, dir):
        # 执行sql命令,sql与sql1效果一样，但是因为数据只有到2023.3，所以sql1有点写死的感觉
        sql='''
        SELECT COUNT(c.contributor) 
        from(
            SELECT t.*
            FROM users_month t 
            JOIN (
                SELECT DISTINCT times
                FROM users_month 
                ORDER BY times DESC
                LIMIT 6
            ) AS latest_dates ON t.times = latest_dates.times 
        ORDER BY t.times DESC) as c 
        WHERE c.item='{}' and table_json='bus_factor_detail'
        '''.format(dir)
        '''
        sql1=SELECT times FROM users_month WHERE item='airflow' AND times>='2022-10-01 00:00:00' and 
        table_json='bus_factor_detail' ORDER BY times DESC
        '''
        return self.inquire_sql(sql)

    # 新增贡献值存留（持续活跃新增贡献值人数）
    def NCR_zsql(self, dir):
        sql='''
        SELECT COUNT(*) AS result
        FROM (
            SELECT DISTINCT n.contributor, n.times
            FROM (
                SELECT `year_month` as times, contributor
                FROM new_contributors 
                WHERE item = '{0}'
                  AND `year_month` >= '2022-10-01 00:00:00'
                  AND `year_month` <= '2022-12-01 00:00:00'
                  AND contributor !='0'
            ) AS n
            JOIN (
                SELECT times, contributor
                FROM users_month 
                WHERE item = '{1}'
                  AND `times` >= '2022-10-01 00:00:00'
                  AND table_json = 'activity_details'
            ) AS u ON n.contributor = u.contributor 
                    AND u.times > n.times
            GROUP BY n.contributor, n.times
            HAVING COUNT(u.contributor) >= 2
        ) AS filtered_data;
        '''.format(dir,dir)
        return self.inquire_sql(sql)

    # 问题响应率
    def IRE_zsql(self, dir):
        sql='''
        SELECT AVG(response_time) AS avg_response_time FROM ir_times  WHERE item='{}';
        '''.format(dir)
        return self.inquire_sql(sql)

    # 贡献值流失率（流失的贡献值人数）
    def CCR_zsql(self, dir):
        sql='''
        SELECT COUNT(DISTINCT q.contributor) AS lose_contributors
        FROM (
            SELECT 
                times,
                contributor
            FROM users_month 
            WHERE item = '{0}'
            AND times >= '2022-04-01 00:00:00'
            AND times <= '2022-12-01 00:00:00'
            AND table_json = 'activity_details'
        ) AS q
        LEFT JOIN (
            SELECT 
                times,
                contributor
            FROM users_month 
            WHERE item = '{1}' 
            AND times >= '2023-01-01 00:00:00'
            AND table_json = 'activity_details'
        ) AS h
        ON q.contributor = h.contributor
        WHERE h.contributor IS NULL
        '''.format(dir,dir)
        return self.inquire_sql(sql)

    # 贡献值流失率（贡献者总人数）
    def CCR_msql(self, dir):
        sql = '''
        SELECT 
        COUNT(DISTINCT contributor)
        FROM users_month 
        WHERE item='{}' 
        AND `times`>='2022-04-01 00:00:00' 
        AND times <= '2022-12-01 00:00:00'
        AND table_json='activity_details'
        '''.format(dir)
        return self.inquire_sql(sql)

    # 项目持续指数，存入数据库
    def CSI_sql(self,item,CCA, NCR,IRE,CCR,CSI):
        sql=f"INSERT IGNORE INTO csi_index (item,CCA, NCR,IRE,CCR,CSI) VALUES ('{item}',{CCA},{NCR},{IRE},{CCR},{CSI})"
        self.cursor.execute(sql)
        db_mysql.con.commit()


class OPEN_IOTDB:
    # 核心贡献值活跃度（总活跃度、评论、pr）
    def cca_z(self,dir):
        sql=f"select code_change_lines_add,change_requests,issue_comments from root.opendigger.{dir} ORDER BY TIME DESC LIMIT 6"
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        # results[np.isnan(results)] = 1.0
        results = [[1.0 if isinstance(x, float) and x != x else x for x in row] for row in results]
        # 获取代码、pr、issue，因为代码、pr、issue三者差距太大，所以归一化
        data = [[row[1:]] for row in results]
        # 归一化，大数字乘以小占比；sum_number本来相用在分母和分子同时除以相同数字，但是分母除以sum_number/6后会远小于分子
        sum_number=0
        avg_sum=0
        for row in data:
            row=row[0]
            total = sum(row)        # 求和用于归一化
            new = [x / total for x in row]      # 每个数字除以总数，得到新列表，为小数
            data_sorted = sorted(row)
            new_sorted = sorted(new, reverse=True)
            result = sum(d * n for d, n in zip(data_sorted, new_sorted))    # 归一化，大乘小，并相加
            sum_number+=total       # 所有未归一化后的提交数(没有用到)
            avg_sum+=result         # 所有归一化后的提交数
        # print(sum_number/6,avg_sum,cca_m)     # avg_sum/contributes为一个项目的CCA
        return avg_sum

    def activity_tend(self):
        ope=OPEN_MYSQL()
        dirs_list = []
        for i in range(100):        # 选择项目个数
            table, goal = com_json.dir_json(i)
            dirs_list.append(table)
        db_iotdb.iotdb()
        self.cursor = db_iotdb.conn.cursor()

        ope.open_mysql()        # 打开数据库连接
        # dirs_list=dirs_list[64:]
        for dir in dirs_list:
            # if dir != 'golang':
            #     continue
            # 核心贡献值活跃度
            cca_z=self.cca_z(dir)
            cca_m = ope.CCA_sql(dir)  # 核心贡献值人数
            # cca = round(cca_z/cca_m, 2)
            cca=cca_z/cca_m
            # 新增贡献值存留
            ncr_z=ope.NCR_zsql(dir)     # 持续活跃三个月新增贡献值人数
            ncr_msql = f"select sum(new_contributors) from root.opendigger.{dir} where time >=2022-10-01T00:00:00.000+08:00" \
                       f" and time <=2022-12-01T00:00:00.000+08:00"
            self.cursor.execute(ncr_msql)
            ncr_m = self.cursor.fetchall()      # 过去六个月新增贡献值人数
            if np.isnan(ncr_m[0][0]):
                ncr_m[0][0] = 0.0
            ncr_m = 1.0 if ncr_m[0][0] == 0.0 else ncr_m[0][0]
            # ncr=round(ncr_z/ncr_m, 2)
            ncr=ncr_z/ncr_m

            # 问题响应率
            ire_z = ope.IRE_zsql(dir)
            ire = max(0, min(1, 1 - (ire_z / 72)))  # 大于1，取1,；小于0，取0    问题响应效率
            # ire=float(round(ire,2))
            ire=float(ire)

            # 贡献值流失率
            ccr_z=ope.CCR_zsql(dir)                 # 过去十二个月流失贡献者人数
            ccr_m = ope.CCR_msql(dir)                 # 对应月份活跃贡献者总人数
            # ccr=round(ccr_z/ccr_m, 2)
            ccr=ccr_z/ccr_m

            # 项目持续指数
            csi=(0.4*cca+0.3*ncr+0.3*ire)/(ccr+0.01)
            print(dir,cca,ncr,ire,ccr,csi)
            ope.CSI_sql(dir,cca,ncr,ire,ccr,csi)
        ope.close_mysql()

        self.cursor.close()
        db_iotdb.conn.close()


# IOTDB插入数据
model=OPEN_IOTDB()
model.activity_tend()




